﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace GeneralMethodClass.Data
{
    /// <summary>
    /// Access数据库操作
    /// </summary>
    public class AccessOP
    {
        public OleDbConnection db;
        OleDbDataReader odrReader;
        DataTable dt;
        DataRow dr;

        public AccessOP(string conn)
        {
            db = new OleDbConnection(conn);
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="field">字段名</param>
        /// <param name="table">表名</param>
        /// <param name="where">条件（NULL为全部）</param>
        /// <returns>DataTable</returns>
        public DataTable Select(string field, string table, string where)
        {
            dt = new DataTable();
            string sql = "";

            if (Text.StringProcess.IsNullOrEmpty(where))
                sql = "SELECT " + field + " FROM " + table;
            else
                sql = "SELECT " + field + " FROM " + table + " WHERE " + where;
            
            OleDbCommand cmd = db.CreateCommand();
            cmd.CommandText = sql;
            
            if (db.State == ConnectionState.Closed) { db.Open(); }

            odrReader = cmd.ExecuteReader();
            int size = odrReader.FieldCount;

            for (int i = 0; i < size; i++)
            {
                DataColumn dc;
                dc = new DataColumn(odrReader.GetName(i));
                dt.Columns.Add(dc);
            }

            while (odrReader.Read())
            {
                dr = dt.NewRow();

                for (int i = 0; i < size; i++)
                {
                    dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString();
                }

                dt.Rows.Add(dr);
            }

            odrReader.Close();

            return dt;
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="field">字段名（以","分隔）</param>
        /// <param name="table">表名</param>
        /// <param name="value">值（形式为：'value 1','value 2'...）</param>
        public void Insert(string field, string table, string value)
        {
            string sql = "INSERT INTO " + table + " (" + field + ") VALUES (" + value + ")";
            OleDbCommand cmd = db.CreateCommand();
            cmd.CommandText = sql;

            if (db.State == ConnectionState.Closed) { db.Open(); }

            cmd.ExecuteReader();
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="field">字段名（以","分隔）</param>
        /// <param name="table">表名</param>
        /// <param name="where">条件（NULL为全部）</param>
        /// <param name="value">值（以","分隔）</param>
        public void Update(string field, string table, string where, string value)
        {
            string sql = "";
            List<string> s = Text.StringProcess.FormatString(field, value, "'");
            string fValue = Text.StringProcess.ConnectString(s, ", ");

            if (Text.StringProcess.IsNullOrEmpty(where))
                sql = "UPDATE " + table + " SET " + fValue;
            else
                sql = "UPDATE " + table + " SET " + fValue + " WHERE " + where;

            OleDbCommand cmd = db.CreateCommand();
            cmd.CommandText = sql;

            if (db.State == ConnectionState.Closed) { db.Open(); }

            cmd.ExecuteReader();
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="where">条件（NULL为全部）</param>
        public void Delete(string table, string where)
        {
            string sql = "";

            if (Text.StringProcess.IsNullOrEmpty(where))
                sql = "DELETE FROM " + table;
            else
                sql = "DELETE FROM " + table + " WHERE " + where;

            OleDbCommand cmd = db.CreateCommand();
            cmd.CommandText = sql;

            if (db.State == ConnectionState.Closed) { db.Open(); }

            cmd.ExecuteReader();
        }

        /// <summary>
        /// 删除表中的重复数据
        /// </summary>
        /// <param name="field">字段名</param>
        /// <param name="table">表名</param>
        public void DeleteRepeat(string field, string table)
        {
            string sql = "DELETE FROM " + table + " WHERE (ID NOT IN (SELECT MIN(ID) FROM " + table + " GROUP BY " + field + "))";
            OleDbCommand cmd = db.CreateCommand();
            cmd.CommandText = sql;

            if (db.State == ConnectionState.Closed) { db.Open(); }

            cmd.ExecuteReader();
        }

        /// <summary>
        /// 格式化Where语句
        /// </summary>
        /// <param name="field">字段名（以","分隔）</param>
        /// <param name="value">值（以","分隔）</param>
        /// <param name="isAnd">关系（And 为 true，Or 为 false）</param>
        /// <returns>string</returns>
        public static string FormatWhere(string field, string value, bool isAnd)
        {
            string where = "";
            List<string> wWhere = Text.StringProcess.FormatString(field, value, "'");

            if (wWhere.Count != 1)
            {
                if (isAnd)
                    where = "((" + Text.StringProcess.ConnectString(wWhere, ") And (") + "))";
                else
                    where = "((" + Text.StringProcess.ConnectString(wWhere, ") Or (") + "))";
            }
            else
            {
                where = "(" + Text.StringProcess.ConnectString(wWhere, ") And (") + ")";
            }

            return where;
        }

        /// <summary>
        /// 获取表中最新记录
        /// </summary>
        /// <param name="field">以指定字段排序</param>
        /// <param name="table">表名</param>
        /// <returns></returns>
        public DataTable GetTopOne(string field, string table)
        {
            dt = new DataTable();
            string sql = "SELECT TOP(1) * FROM " + table + " ORDER BY " + field + " DESC";

            OleDbCommand cmd = db.CreateCommand();
            cmd.CommandText = sql;

            if (db.State == ConnectionState.Closed) { db.Open(); }

            odrReader = cmd.ExecuteReader();
            int size = odrReader.FieldCount;

            for (int i = 0; i < size; i++)
            {
                DataColumn dc = null;
                dc = new DataColumn(odrReader.GetName(i));
                dt.Columns.Add(dc);
            }

            while (odrReader.Read())
            {
                dr = dt.NewRow();

                for (int i = 0; i < size; i++)
                {
                    dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString();
                }

                dt.Rows.Add(dr);
            }

            odrReader.Close();
            return dt;
        }

        /// <summary>
        /// 获取某一字段中的最大值
        /// </summary>
        /// <param name="field">字段名</param>
        /// <param name="table">表名</param>
        /// <param name="alias">别名</param>
        /// <returns></returns>
        public DataTable GetMax(string field, string table, string alias)
        {
            dt = new DataTable();
            string sql = "SELECT MAX(" + field + ") AS " + alias + " FROM " + table;

            OleDbCommand cmd = db.CreateCommand();
            cmd.CommandText = sql;

            if (db.State == ConnectionState.Closed) { db.Open(); }

            odrReader = cmd.ExecuteReader();
            int size = odrReader.FieldCount;

            for (int i = 0; i < size; i++)
            {
                DataColumn dc = null;
                dc = new DataColumn(odrReader.GetName(i));
                dt.Columns.Add(dc);
            }

            while (odrReader.Read())
            {
                dr = dt.NewRow();

                for (int i = 0; i < size; i++)
                {
                    dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString();
                }

                dt.Rows.Add(dr);
            }

            odrReader.Close();
            return dt;
        }
    }
}
